<!DOCTYPE html>
<html lang="zh-CN">
    <head>
        <meta charset="utf-8">
        <meta name="viewport" content="width=device-width, initial-scale=1">
        <meta name="robots" content="noodp" />
        <meta http-equiv="X-UA-Compatible" content="IE=edge, chrome=1">
        <title>MySQL- 技术专题 - 主从复制原理 - 负责和大饼的博客</title><meta name="Description" content="想专注于学点手艺"><meta property="og:title" content="MySQL- 技术专题 - 主从复制原理" />
<meta property="og:description" content="MySQL- 技术专题 - 主从复制原理 原理 MySql主库在事务提交时会把数据变更作为事件记录在二进制日志Binlog中； 主库推送二进制日志文件Binlog" />
<meta property="og:type" content="article" />
<meta property="og:url" content="https://amath0312.github.com/mysql-master-slave-copy/" />
<meta property="og:image" content="https://amath0312.github.com/mysql-master-slave-copy/assets/438ce11b10f86533fab4da0e247177be-20201018180506092.png"/>
<meta property="article:published_time" content="2020-10-18T17:50:03+08:00" />
<meta property="article:modified_time" content="2020-10-18T17:50:03+08:00" />
<meta name="twitter:card" content="summary_large_image"/>
<meta name="twitter:image" content="https://amath0312.github.com/mysql-master-slave-copy/assets/438ce11b10f86533fab4da0e247177be-20201018180506092.png"/>
<meta name="twitter:title" content="MySQL- 技术专题 - 主从复制原理"/>
<meta name="twitter:description" content="MySQL- 技术专题 - 主从复制原理 原理 MySql主库在事务提交时会把数据变更作为事件记录在二进制日志Binlog中； 主库推送二进制日志文件Binlog"/>
<meta name="application-name" content="负责和大饼的博客">
<meta name="apple-mobile-web-app-title" content="负责和大饼的博客"><link rel="shortcut icon" type="image/x-icon" href="/favicon.ico" />
        <link rel="icon" type="image/png" sizes="32x32" href="/favicon-32x32.png">
        <link rel="icon" type="image/png" sizes="16x16" href="/favicon-16x16.png"><link rel="apple-touch-icon" sizes="180x180" href="/apple-touch-icon.png"><link rel="manifest" href="/site.webmanifest"><link rel="canonical" href="https://amath0312.github.com/mysql-master-slave-copy/" /><link rel="prev" href="https://amath0312.github.com/how-to-use/" /><link rel="next" href="https://amath0312.github.com/about-cache-line/" /><link rel="stylesheet" href="/lib/normalize/normalize.min.css"><link rel="stylesheet" href="/css/style.min.css"><link rel="stylesheet" href="/lib/fontawesome-free/all.min.css"><link rel="stylesheet" href="/lib/animate/animate.min.css"><script type="application/ld+json">
    {
        "@context": "http://schema.org",
        "@type": "BlogPosting",
        "headline": "MySQL- 技术专题 - 主从复制原理",
        "inLanguage": "zh-CN",
        "mainEntityOfPage": {
            "@type": "WebPage",
            "@id": "https:\/\/amath0312.github.com\/mysql-master-slave-copy\/"
        },"image": [{
                            "@type": "ImageObject",
                            "url": "https:\/\/amath0312.github.com\/mysql-master-slave-copy\/assets\/438ce11b10f86533fab4da0e247177be-20201018180506092.png",
                            "width":  561 ,
                            "height":  332 
                        }],"genre": "posts","keywords": "mysql, 主从复制","wordcount":  672 ,
        "url": "https:\/\/amath0312.github.com\/mysql-master-slave-copy\/","datePublished": "2020-10-18T17:50:03+08:00","dateModified": "2020-10-18T17:50:03+08:00","publisher": {
            "@type": "Organization",
            "name": "负责和大饼"},"author": {
                "@type": "Person",
                "name": "负责和大饼"
            },"description": ""
    }
    </script></head>
    <body header-desktop="" header-mobile=""><script type="text/javascript">(window.localStorage && localStorage.getItem('theme') ? localStorage.getItem('theme') === 'dark' : ('' === 'auto' ? window.matchMedia('(prefers-color-scheme: dark)').matches : '' === 'dark')) && document.body.setAttribute('theme', 'dark');</script>

        <div id="mask"></div><div class="wrapper"><header class="desktop" id="header-desktop">
    <div class="header-wrapper">
        <div class="header-title">
            <a href="/" title="负责和大饼的博客">负责和大饼的博客</a>
        </div>
        <div class="menu">
            <div class="menu-inner"><a class="menu-item" href="/posts/"> Blog </a><a class="menu-item" href="/categories/"> Categories </a><a class="menu-item" href="/tags/"> Tags </a><a class="menu-item" href="/about/"> About </a><span class="menu-item delimiter"></span><span class="menu-item search" id="search-desktop">
                        <input type="text" placeholder="Search titles or contents..." id="search-input-desktop">
                        <a href="javascript:void(0);" class="search-button search-toggle" id="search-toggle-desktop" title="Search">
                            <i class="fas fa-search fa-fw"></i>
                        </a>
                        <a href="javascript:void(0);" class="search-button search-clear" id="search-clear-desktop" title="Clear">
                            <i class="fas fa-times-circle fa-fw"></i>
                        </a>
                        <span class="search-button search-loading" id="search-loading-desktop">
                            <i class="fas fa-spinner fa-fw fa-spin"></i>
                        </span>
                    </span><a href="javascript:void(0);" class="menu-item theme-switch" title="Switch Theme">
                    <i class="fas fa-adjust fa-fw"></i>
                </a>
            </div>
        </div>
    </div>
</header><header class="mobile" id="header-mobile">
    <div class="header-container">
        <div class="header-wrapper">
            <div class="header-title">
                <a href="/" title="负责和大饼的博客">负责和大饼的博客</a>
            </div>
            <div class="menu-toggle" id="menu-toggle-mobile">
                <span></span><span></span><span></span>
            </div>
        </div>
        <div class="menu" id="menu-mobile"><div class="search-wrapper">
                    <div class="search mobile" id="search-mobile">
                        <input type="text" placeholder="Search titles or contents..." id="search-input-mobile">
                        <a href="javascript:void(0);" class="search-button search-toggle" id="search-toggle-mobile" title="Search">
                            <i class="fas fa-search fa-fw"></i>
                        </a>
                        <a href="javascript:void(0);" class="search-button search-clear" id="search-clear-mobile" title="Clear">
                            <i class="fas fa-times-circle fa-fw"></i>
                        </a>
                        <span class="search-button search-loading" id="search-loading-mobile">
                            <i class="fas fa-spinner fa-fw fa-spin"></i>
                        </span>
                    </div>
                    <a href="javascript:void(0);" class="search-cancel" id="search-cancel-mobile">
                        Cancel
                    </a>
                </div><a class="menu-item" href="/posts/" title="">Blog</a><a class="menu-item" href="/categories/" title="">Categories</a><a class="menu-item" href="/tags/" title="">Tags</a><a class="menu-item" href="/about/" title="">About</a><a href="javascript:void(0);" class="menu-item theme-switch" title="Switch Theme">
                <i class="fas fa-adjust fa-fw"></i>
            </a></div>
    </div>
</header>
<div class="search-dropdown desktop">
    <div id="search-dropdown-desktop"></div>
</div>
<div class="search-dropdown mobile">
    <div id="search-dropdown-mobile"></div>
</div>
<main class="main">
                <div class="container"><div class="toc" id="toc-auto">
            <h2 class="toc-title">Contents</h2>
            <div class="toc-content" id="toc-content-auto"></div>
        </div><article class="page single"><h1 class="single-title animated flipInX">MySQL- 技术专题 - 主从复制原理</h1><div class="post-meta">
            <div class="post-meta-line"><span class="post-author"><a href="/" title="Author" rel=" author" class="author"><i class="fas fa-user-circle fa-fw"></i>负责和大饼</a></span>&nbsp;<span class="post-category">included in <a href="/categories/%E5%BC%80%E5%8F%91/"><i class="far fa-folder fa-fw"></i>开发</a>&nbsp;<a href="/categories/%E6%95%B0%E6%8D%AE%E5%BA%93/"><i class="far fa-folder fa-fw"></i>数据库</a></span></div>
            <div class="post-meta-line"><i class="far fa-calendar-alt fa-fw"></i>&nbsp;<time datetime="2020-10-08">2020-10-08</time>&nbsp;<i class="fas fa-pencil-alt fa-fw"></i>&nbsp;672 words&nbsp;
                <i class="far fa-clock fa-fw"></i>&nbsp;2 minutes&nbsp;</div>
        </div><div class="featured-image"><img
        class="lazyload"
        src="/svg/loading.min.svg"
        data-src="/mysql-master-slave-copy/assets/438ce11b10f86533fab4da0e247177be-20201018180506092.png"
        data-srcset="/mysql-master-slave-copy/assets/438ce11b10f86533fab4da0e247177be-20201018180506092.png, /mysql-master-slave-copy/assets/438ce11b10f86533fab4da0e247177be-20201018180506092.png 1.5x, /mysql-master-slave-copy/assets/438ce11b10f86533fab4da0e247177be-20201018180506092.png 2x"
        data-sizes="auto"
        alt="/mysql-master-slave-copy/assets/438ce11b10f86533fab4da0e247177be-20201018180506092.png"
        title="/mysql-master-slave-copy/assets/438ce11b10f86533fab4da0e247177be-20201018180506092.png" /></div><div class="details toc" id="toc-static"  kept="">
                <div class="details-summary toc-title">
                    <span>Contents</span>
                    <span><i class="details-icon fas fa-angle-right"></i></span>
                </div>
                <div class="details-content toc-content" id="toc-content-static"><nav id="TableOfContents">
  <ul>
    <li><a href="#原理">原理</a></li>
    <li><a href="#搭建主库">搭建主库</a></li>
    <li><a href="#搭建从库">搭建从库</a></li>
  </ul>
</nav></div>
            </div><div class="content" id="content"><h1 id="mysql--技术专题---主从复制原理">MySQL- 技术专题 - 主从复制原理</h1>
<h2 id="原理">原理</h2>
<ul>
<li>
<p><code>MySql</code>主库在事务提交时会把数据变更作为事件记录在二进制日志<code>Binlog</code>中；</p>
</li>
<li>
<p>主库推送二进制日志文件Binlog中的事件到从库的中继日志<code>Relay Log</code>中，之后从库根据中继日志重做数据变更操作，通过逻辑复制来达到主库和从库的数据一致性；</p>
</li>
<li>
<p><code>MySQL</code>通过三个线程来完成主从库间的数据复制，其中<code>Binlog Dump</code>线程跑在主库上，I/O线程和SQL线程跑着从库上；</p>
</li>
<li>
<p>当在从库上启动复制时，首先创建I/O线程连接主库，主库随后创建<code>Binlog Dump</code>线程读取数据库事件并发送给I/O线程，I/O线程获取到事件数据后更新到从库的中继日志<code>Relay Log</code>中去，之后从库上的SQL线程读取中继日志<code>Relay Log</code>中更新的数据库事件并应用，如下图所示。</p>
</li>
</ul>
<p><!-- raw HTML omitted --></p>
<h2 id="搭建主库">搭建主库</h2>
<ol>
<li>设置主库server_id；</li>
<li>启用binlog；</li>
<li>指定不需要同步的数据库名称；</li>
<li>跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断，如<code>1062</code>错误是指一些主键重复，<code>1032</code>错误是因为主从数据库数据不一致；</li>
<li>创建数据同步用户，并授予权限；</li>
<li>检查主库状态：<code>show master status</code></li>
</ol>
<h2 id="搭建从库">搭建从库</h2>
<ol>
<li>
<p>配置主库<code>server_id</code>；</p>
</li>
<li>
<p>启用binlog；</p>
</li>
<li>
<p>指定不需要同步的数据库名称；</p>
</li>
<li>
<p>跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断；</p>
</li>
<li>
<p>配置中继日志<code>relay_log</code>；</p>
</li>
<li>
<p>slave库设置为只读</p>
</li>
<li>
<p>从库配置主从复制：</p>
<div class="highlight"><pre class="chroma"><code class="language-sql" data-lang="sql"><span class="n">change</span> <span class="n">master</span> <span class="k">to</span> <span class="n">master_host</span><span class="o">=</span><span class="s1">&#39;192.168.6.132&#39;</span><span class="p">,</span> <span class="n">master_user</span><span class="o">=</span><span class="s1">&#39;slave&#39;</span><span class="p">,</span> <span class="n">master_password</span><span class="o">=</span><span class="s1">&#39;123456&#39;</span><span class="p">,</span> <span class="n">master_port</span><span class="o">=</span><span class="mi">3307</span><span class="p">,</span> <span class="n">master_log_file</span><span class="o">=</span><span class="s1">&#39;mall-mysql-bin.000001&#39;</span><span class="p">,</span> <span class="n">master_log_pos</span><span class="o">=</span><span class="mi">617</span><span class="p">,</span> <span class="n">master_connect_retry</span><span class="o">=</span><span class="mi">30</span><span class="p">;</span>  
</code></pre></div></li>
<li>
<p>查看主从同步状态：<code>show slave status \G;</code>，关注<code>Slave_IO_Running</code>，<code>Slave_SQL_Running</code> ，<code>Yes</code>表示已启动</p>
</li>
<li>
<p>启动主从同步：<code>start slave;</code></p>
</li>
</ol>
</div><div class="post-footer" id="post-footer">
    <div class="post-info">
        <div class="post-info-line">
            <div class="post-info-mod">
                <span>Updated on 2020-10-08</span>
            </div>
            <div class="post-info-license"></div>
        </div>
        <div class="post-info-line">
            <div class="post-info-md"></div>
            <div class="post-info-share">
                <span></span>
            </div>
        </div>
    </div>

    <div class="post-info-more">
        <section class="post-tags"><i class="fas fa-tags fa-fw"></i>&nbsp;<a href="/tags/mysql/">mysql</a>,&nbsp;<a href="/tags/%E4%B8%BB%E4%BB%8E%E5%A4%8D%E5%88%B6/">主从复制</a></section>
        <section>
            <span><a href="javascript:void(0);" onclick="window.history.back();">Back</a></span>&nbsp;|&nbsp;<span><a href="/">Home</a></span>
        </section>
    </div>

    <div class="post-nav"><a href="/how-to-use/" class="prev" rel="prev" title="hugo日常使用总结"><i class="fas fa-angle-left fa-fw"></i>hugo日常使用总结</a>
            <a href="/about-cache-line/" class="next" rel="next" title="Cache Line对读写性能的影响">Cache Line对读写性能的影响<i class="fas fa-angle-right fa-fw"></i></a></div>
</div>
</article></div>
            </main><footer class="footer">
        <div class="footer-container"><div class="footer-line">Powered by <a href="https://gohugo.io/" target="_blank" rel="noopener noreffer" title="Hugo 0.75.1">Hugo</a> | Theme - <a href="https://github.com/dillonzq/LoveIt" target="_blank" rel="noopener noreffer" title="LoveIt 0.2.10"><i class="far fa-kiss-wink-heart fa-fw"></i> LoveIt</a>
                </div><div class="footer-line"><i class="far fa-copyright fa-fw"></i><span itemprop="copyrightYear">2019 - 2020</span><span class="author" itemprop="copyrightHolder">&nbsp;<a href="/" target="_blank"></a></span>&nbsp;|&nbsp;<span class="license">本文采用<a rel="license" href="http://creativecommons.org/licenses/by-nc/4.0/" target="_blank">知识共享署名-非商业性使用 4.0 国际许可协议</a>进行许可</span></div>
        </div>
    </footer></div>

        <div id="fixed-buttons"><a href="#" id="back-to-top" class="fixed-button" title="Back to Top">
                <i class="fas fa-arrow-up fa-fw"></i>
            </a><a href="#" id="view-comments" class="fixed-button" title="View Comments">
                <i class="fas fa-comment fa-fw"></i>
            </a>
        </div><script type="text/javascript" src="/lib/smooth-scroll/smooth-scroll.min.js"></script><script type="text/javascript" src="/lib/autocomplete/autocomplete.min.js"></script><script type="text/javascript" src="/lib/algoliasearch/algoliasearch-lite.umd.min.js"></script><script type="text/javascript" src="/lib/lazysizes/lazysizes.min.js"></script><script type="text/javascript" src="/lib/clipboard/clipboard.min.js"></script><script type="text/javascript">window.config={"code":{"copyTitle":"Copy to clipboard","maxShownLines":10},"comment":{},"search":{"algoliaAppID":"JJRDOPDPLW","algoliaIndex":"amath_blog","algoliaSearchKey":"e529bedc6a3af35b53e2597f1c7550c2","highlightTag":"em","maxResultLength":10,"noResultsFound":"No results found","snippetLength":50,"type":"algolia"}};</script><script type="text/javascript" src="/js/theme.min.js"></script></body>
</html>
